package edu.software.PBL_Management_System.demand.repository;

import edu.software.PBL_Management_System.entity.Demand;
import edu.software.PBL_Management_System.entity.User;
import org.apache.ibatis.annotations.Param;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;

@Repository
public interface DemandRepository extends JpaRepository<Demand,Long> {
    // 使用 EntityGraph 优化查询，一次性加载creator和tags
    @EntityGraph(attributePaths = {"creator", "tags"})
    List<Demand> findByCreator(User creator);

    // 分页查询也使用 EntityGraph
    @EntityGraph(attributePaths = {"creator", "tags"})
    Page<Demand> findByCreator(User creator, Pageable pageable);

    @EntityGraph(attributePaths = {"creator", "tags"})
    List<Demand> findByStatus(Demand.DemandStatus status);

    @EntityGraph(attributePaths = {"creator", "tags"})
    List<Demand> findByParentId(Long parentId);

    @EntityGraph(attributePaths = {"creator", "tags"})
    List<Demand> findByParentIdIsNull();

    @EntityGraph(attributePaths = {"creator", "tags"})
    Page<Demand> findByParentIdIsNull(Pageable pageable);

    @EntityGraph(attributePaths = {"creator", "tags"})
    Page<Demand> findByParentIdIsNullAndCreatorIdNot(Long creatorId, Pageable pageable);

    @EntityGraph(attributePaths = {"creator", "tags"})
    List<Demand> findByTitleContaining(String title);

    @EntityGraph(attributePaths = {"creator", "tags"})
    List<Demand> findByCreatorAndStatus(User creator, Demand.DemandStatus status);

    // 优化查询，直接在SQL中关联必要的表
    @Query("SELECT new map(d.id as id, d.title as title, d.status as status, " +
            "d.createdTime as createdTime, u.id as creatorId, u.username as creatorName, " +
            "u.avatarUrl as creatorAvatar) " +
            "FROM Demand d JOIN d.creator u WHERE d.creator = :creator")
    List<Map<String, Object>> findBasicInfoByCreator(@Param("creator") User creator);

    @Query("SELECT new map(d.id as id, d.title as title, d.status as status, " +
            "d.createdTime as createdTime, u.id as creatorId, u.username as creatorName, " +
            "u.avatarUrl as creatorAvatar) " +
            "FROM Demand d JOIN d.creator u WHERE d.creator = :creator AND d.parentId IS NULL")
    List<Map<String, Object>> findTopLevelBasicInfoByCreator(@Param("creator") User creator);

    @Query("SELECT new map(d.id as id, d.title as title, d.status as status, " +
            "d.createdTime as createdTime, u.id as creatorId, u.username as creatorName, " +
            "u.avatarUrl as creatorAvatar) " +
            "FROM Demand d JOIN d.creator u WHERE d.creator = :creator AND d.status = :status")
    List<Map<String, Object>> findBasicInfoByCreatorAndStatus(
            @Param("creator") User creator,
            @Param("status") Demand.DemandStatus status);

    @Query("SELECT new map(d.id as id, d.title as title, d.description as description, " +
            "d.status as status, d.createdTime as createdTime, d.mindMapData as mindMapData, " +
            "d.version as version, d.parentId as parentId, u.id as creatorId, " +
            "u.username as creatorName, u.avatarUrl as creatorAvatar, " +
            "(SELECT p.title FROM Demand p WHERE p.id = d.parentId) as parentTitle) " +
            "FROM Demand d JOIN d.creator u WHERE d.id = :id")
    Map<String, Object> findDetailedInfoById(@Param("id") Long id);

    @Query("SELECT new map(d.id as id, d.title as title, d.mindMapData as mindMapData, " +
            "u.id as creatorId, u.username as creatorName, u.avatarUrl as creatorAvatar) " +
            "FROM Demand d JOIN d.creator u WHERE d.id = :id")
    Map<String, Object> findMindmapDataById(@Param("id") Long id);

    @Query("SELECT DISTINCT d FROM Demand d JOIN FETCH d.creator JOIN FETCH d.tags " +
            "JOIN Comment c ON d.id = c.demandId")
    List<Demand> findAllCommentedDemands();
}
